﻿Module StateDao
    Public Function searchDataByFilter(ByRef state As State) As Data.DataTable
        DataAccessConnection.openConnection()

        Dim command As New OracleClient.OracleCommand
        Dim table As New Data.DataTable

        Dim query As String = ""

        query = "SELECT * FROM " & state.getTableName & " WHERE "
        query &= " name like '%" & state.nameProperty & "%'"
        query &= " AND (id = " & state.idProperty & " OR " & state.idProperty & " = 0)"
        query &= " AND (sts_id = " & state.stateScopeProperty.idProperty & " OR " & state.stateScopeProperty.idProperty & " = 0)"

        command.CommandType = CommandType.Text
        command.CommandText = query
        command.Connection = connection

        table.Load(command.ExecuteReader)

        DataAccessConnection.closeConnection()

        Return table
    End Function

    Public Function searchDataById(ByRef id As Integer) As State
        Dim table_states As New Data.DataTable

        Dim state As New State()
        state.idProperty = id
        Dim stateScope As New StateScope(stateScope.STATE_SCOPE_TASK)
        state.stateScopeProperty = stateScope

        table_states = searchDataByFilter(state)
        state = convertTableToState(table_states)
        Return state
    End Function

    Private Function convertTableToState(ByRef table_states As Data.DataTable) As State
        Dim state As New State
        state.idProperty = table_states.Rows(0)("id")
        state.nameProperty = table_states.Rows(0)("name")

        Return state
    End Function
End Module
